import os
import sys
import tempfile
import uuid
sys.path.append(os.path.join(os.path.dirname(os.path.dirname(os.path.realpath(__file__))),'modules'))
sys.path.append(os.path.join(os.path.dirname(os.path.dirname(os.path.realpath(__file__)))))
import ganzhi
import week_yun
import ktt_order_export
import ktt_core
import ktt_core_new
import pandas as pd
import read_config
from flask import Flask, request,jsonify, Response,render_template,send_file,make_response
import zipfile
import io
from datetime import datetime,timedelta
import json
import xlwings as xw
import openpyxl
from openpyxl.utils.dataframe import dataframe_to_rows
import pymysql
import platform
if platform.system().lower()=='windows':
    import pythoncom
    import win32com.client
elif platform.system().lower()=='darwin':
    pass

import dd_ppt
import base64
import subprocess
import random

from PIL import Image
import re

class EjService(Flask):

    def __init__(self,*args,**kwargs):
        super(EjService, self).__init__(*args, **kwargs)
        config_fn=os.path.join(os.path.dirname(os.path.dirname(os.path.realpath(__file__))),'configs','ej_service.config')
        self.config_ej=read_config.read_json(config_fn)

        #读取快团团有关配置文件存放路径
        ktt_config=os.path.join(os.path.dirname(__file__),'config','ktt.config')
        with open(ktt_config,'r',encoding='utf-8') as f:
            self.ktt_config=json.loads(f.read())

        # print(self.ktt_config)
        #读取不同发货商导表时与快团团对应的规格名称
        with open(self.ktt_config['ktt_col_map_config'],'r',encoding='utf-8') as f:
            self.col_map_config=json.loads(f.read())

        # 读取不同发货商发货表的列名
        with open(self.ktt_config['col_config_fn'],'r',encoding='utf-8') as f:
            self.config_ktt_order=json.loads(f.read())

        # 读取规格的默认名称返回前端
        with open(self.ktt_config['page_config_fn'],'r',encoding='utf-8') as f:
            config_page_default=json.loads(f.read())
        self.spec0=config_page_default['spec0']
        self.spec1=config_page_default['spec1']
        self.fn_info=config_page_default['fn_info']
        self.sender_name=config_page_default['sender_name']
        self.sender_tel=config_page_default['sender_tel']

        
        #路由
        #渲染页面
        #首页

        
        #快团团新页面
        self.add_url_rule('/new_ktt_page',view_func=self.new_ktt_page,methods=['GET','POST'])
        #快团团新页面设置
        self.add_url_rule('/get_new_ktt_page_config',view_func=self.get_new_ktt_page_config,methods=['GET','POST'])      
        #快团团新页面处理
        self.add_url_rule('/deal_submit_ktt_orders',view_func=self.deal_submit_ktt_orders,methods=['GET','POST'])       
        #下载订单文件
        self.add_url_rule('/download_orders', view_func=self.download_orders, methods=['GET'])
        self.add_url_rule('/new_ktt_deal_xlsx_from_page', view_func=self.new_ktt_deal_xlsx_from_page, methods=['POST'])
        #快团团订单下载指南
        self.add_url_rule('/how_to_exp_order', view_func=self.how_to_exp_order, methods=['GET'])

    def how_to_exp_order(self):
       return render_template('/how_to_exp_order.html')     
   
       
    def new_ktt_deal_xlsx_from_page(self):
        file = request.files.get('file')
        seq_no = request.form.get('seq_no', '01')  # 默认为01
        if not file or not file.filename.endswith('.xlsx'):
            return jsonify({'res': 'failed', 'msg': '请上传有效的xlsx文件'})

        try:
            # 读取xlsx文件
            df = self._read_xlsx_from_page(file, password='7788', sheet_name='商品列表')
            
            # 从文件名提取8位日期作为folder_date
            if not re.match(r'^\d{8}', file.filename):
                raise Exception("文件名格式错误，必须以8位数字开头")
            folder_date = file.filename[:8]
            
            # 处理订单
            res = ktt_core_new.orders(
                config_dir=self.ktt_config['syn_config_root'],
                save_temp_root_dir=self.ktt_config['export_root_dir']
            ).exp_order(df=df, seq_no=seq_no, expand_qty='yes', return_stream=True, folder_date=folder_date)

            # 生成zip文件
            zip_buffer, zip_filename = self._generate_orders_zip(res)
            if not zip_buffer:
                return jsonify({'res': 'failed', 'msg': '生成订单文件失败'})

            # 保存zip文件
            temp_dir = os.path.join(self.ktt_config['export_root_dir'], 'temp')
            os.makedirs(temp_dir, exist_ok=True)
            zip_path = os.path.join(temp_dir, zip_filename)
            with open(zip_path, 'wb') as f:
                f.write(zip_buffer.getvalue())

            # 准备统计数据
            total_orders = len(df)
            success_orders = total_orders  # 假设全部成功
            products = {}
            combined_stats = []
            
            for product_name, product_data in res.items():
                if 'total_txt' in product_data:
                    combined_stats.append(f"{product_name}\n{product_data['total_txt']}")
                products[product_name] = len(product_data['df'])

            # 返回结果
            return jsonify({
                'res': 'ok',
                'data': {
                    'total_orders': total_orders,
                    'success_orders': success_orders,
                    'failed_orders': 0,
                    'products': [{'name':k, 'count':v} for k,v in products.items()],
                    'combined_stats_text': "\n\n".join(combined_stats),
                    'download_url': f'/download_orders?filename={zip_filename}'
                }
            })

        except Exception as e:
            return jsonify({'res': 'failed', 'msg': f'处理文件失败: {str(e)}'})
    
    def _read_xlsx_from_page(self, file_obj, password='7788', sheet_name='商品列表'):
        temp_path = None
        try:
            # 先尝试无密码读取(检查文件是否有密码保护)
            try:
                file_obj.seek(0)
                pd.read_excel(file_obj, sheet_name=sheet_name, engine='openpyxl')
                file_obj.seek(0)
                # 无密码保护，直接读取
                return pd.read_excel(file_obj, sheet_name=sheet_name, engine='openpyxl')
            except:
                # 文件可能有密码保护
                if platform.system().lower() == 'windows':
                    try:
                        pythoncom.CoInitialize()
                        # 创建临时文件处理
                        temp_path = os.path.join(tempfile.gettempdir(), f'temp_{uuid.uuid4().hex}.xlsx')
                        # 确保以二进制模式保存文件
                        with open(temp_path, 'wb') as f:
                            file_obj.seek(0)
                            f.write(file_obj.read())
                        
                        # 验证文件完整性
                        if not os.path.exists(temp_path) or os.path.getsize(temp_path) == 0:
                            raise Exception("临时文件保存失败")
                        
                        xlsApp = win32com.client.DispatchEx('Excel.Application')
                        xlsApp.EnableEvents = False
                        xlsApp.DisplayAlerts = False
                        
                        try:
                            xlwb = xlsApp.Workbooks.Open(
                                temp_path,
                                UpdateLinks=False,
                                ReadOnly=False,
                                Password=password
                            )
                        except Exception as e:
                            # 重试一次
                            try:
                                xlwb = xlsApp.Workbooks.Open(
                                    temp_path,
                                    UpdateLinks=False,
                                    ReadOnly=False,
                                    Password=password
                                )
                            except Exception as e2:
                                raise Exception(f"无法打开Excel文件(重试后失败): {str(e2)}")
                        
                        # 读取数据到DataFrame
                        df = pd.DataFrame(list(xlwb.Sheets(sheet_name).UsedRange.Value))
                        df.columns = df.iloc[0]
                        df = df[1:]
                        
                        xlwb.Close(False)
                        xlsApp.Quit()
                        return df
                    except Exception as e:
                        raise Exception(f"无法打开密码保护的Excel文件: {str(e)}")
                    finally:
                        pythoncom.CoUninitialize()
                        if temp_path and os.path.exists(temp_path):
                            try:
                                os.remove(temp_path)
                            except Exception as e:
                                print(f"Error removing temp file {temp_path}: {str(e)}")
                else:
                    raise Exception("非Windows系统无法处理密码保护的Excel文件")
        except Exception as e:
            raise Exception(f"处理Excel文件失败: {str(e)}")
    
    def new_ktt_page(self):
        return render_template('/new_ktt_deal.html')
    
    def get_new_ktt_page_config(self):
        res=ktt_core_new.orders(config_dir=self.ktt_config['syn_config_root'],save_temp_root_dir=self.ktt_config['export_root_dir']). exp_html_config()
        # print('html goods config:',res)

        return jsonify({'res':'ok','data':res})
    
    def _generate_orders_zip(self, data):
        """生成订单zip文件"""
        if not data or not isinstance(data, dict):
            return None, '无效的订单数据'
        
        try:
            # 检查所有文件流是否有效
            for product_name, product_data in data.items():
                if not hasattr(product_data.get('file_stream'), 'getvalue'):
                    return None, f'{product_name}的文件流无效'
                if not product_data.get('output_path'):
                    return None, f'{product_name}的输出路径缺失'

            # 生成zip文件
            # 从第一个文件名中提取前8位作为日期
            first_product = next(iter(data.values()))
            # print('out put path:',first_product['output_path'])
            filename = os.path.basename(first_product['output_path'])  # 获取纯文件名
            date_str = filename.split('-')[0][:8]  # 提取前8位日期
            # date_str = first_product['output_path'].split('\\')[-1].split('-')[0][:8]
            datetime_str = datetime.now().strftime("%Y%m%d%H%M%S")
            zip_filename = f"{date_str}-export-orders.zip"
            # 彻底清理文件名，去除所有可能的多余下划线
            zip_filename = zip_filename.strip('_').replace('__', '_')
            zip_buffer = io.BytesIO()
            
            with zipfile.ZipFile(zip_buffer, 'w', zipfile.ZIP_DEFLATED) as zip_file:
                for product_name, product_data in data.items():
                    file_name = product_data['output_path'].split('\\')[-1]
                    file_content = product_data['file_stream'].getvalue()
                    if not file_content:
                        return None, f'{product_name}的文件内容为空'
                    # 在zip中使用文件名的前8位作为文件夹
                    zip_path = f"{date_str}/{file_name}"
                    zip_file.writestr(zip_path, file_content)
            
            zip_buffer.seek(0)
            return zip_buffer, zip_filename

        except Exception as e:
            return None, f'生成压缩文件失败: {str(e)}'

    def deal_submit_ktt_orders(self):
        data = request.json
        print('received data:', data)
        
        # 初始化统计数据
        stats = {
            'total_orders': 0,
            'success_orders': 0,
            'failed_orders': 0,
            'products': {},
            'combined_stats_text':'',
            'zip_filename': ''
        }
        
        # 处理订单数据
        all_orders = []
        error_msgs = []
        for order in data['orders']:
            stats['total_orders'] += 1
            try:
                # 预处理：过滤空行
                lines = [line.strip() for line in order['addresses'].split('\n') if line.strip()]
                
                # 检查行数是否为偶数
                if len(lines) % 2 != 0:
                    error_msgs.append("订单信息不完整，最后一条订单缺少收货信息")
                    stats['failed_orders'] += (len(lines) + 1) // 2  # 预估失败订单数
                    continue
                
                # 每2行一组处理
                for i in range(0, len(lines), 2):
                    try:
                        # 校验订单编号行
                        if not lines[i].startswith('订单编号：'):
                            raise ValueError(f"第{i+1}行：无效的订单编号格式")
                        order_no = lines[i][5:].strip()  # 去掉"订单编号："前缀
                            
                        # 校验收货信息行
                        if not lines[i+1].startswith('收货信息：'):
                            raise ValueError(f"第{i+2}行：缺少收货信息前缀")
                            
                        # 解析收货信息
                        addr_line = lines[i+1][5:].strip()  # 去掉"收货信息："前缀
                        normalized_line = addr_line.replace('，', ',')
                        parts = normalized_line.rsplit(',', 1)
                        if len(parts) != 2:
                            raise ValueError("缺少数量分隔符逗号")
                            
                        address_info, quantity = parts
                        address_parts = address_info.split(' ')
                        if len(address_parts) < 3:
                            raise ValueError("收货信息格式不正确")
                            
                        # 构建订单记录
                        all_orders.append({
                            '订单号': order_no,
                            '分类': order['category'],
                            '商品': order['spec'], 
                            '商品编码': order['product_id'],
                            '数量': quantity,
                            '收货人': address_parts[0],
                            '联系电话': address_parts[1],
                            '详细地址': ' '.join(address_parts[2:])
                        })
                        stats['success_orders'] += 1
                        
                        # 统计商品
                        if order['category'] not in stats['products']:
                            stats['products'][order['category']] = 0
                        stats['products'][order['category']] += 1
                    except Exception as e:
                        error_msgs.append(str(e))
                        stats['failed_orders'] += 1
            except Exception as e:
                error_msgs.append(str(e))
                stats['failed_orders'] += (len(lines) + 1) // 2  # 预估失败订单数
                print(f"处理订单失败: {str(e)}")
        
        # 转换为DataFrame
        df = pd.DataFrame(all_orders)
        print('Processed orders:\n', df)

        res=ktt_core_new.orders(config_dir=self.ktt_config['syn_config_root'],save_temp_root_dir=self.ktt_config['export_root_dir']).exp_order(df=df, seq_no=data['seq_no'], expand_qty='yes', return_stream=True)

        # print('exp order res:',res)
        # 生成zip文件
        zip_buffer, error_msg = self._generate_orders_zip(res)
        if not zip_buffer:
            return jsonify({'res': 'failed', 'msg': error_msg})
        
        stats['zip_filename'] = error_msg.strip('_').replace('__', '_')
        
        # 保存zip文件到临时目录
        temp_dir = os.path.join(self.ktt_config['export_root_dir'], 'temp')
        os.makedirs(temp_dir, exist_ok=True)
        zip_path = os.path.join(temp_dir, stats['zip_filename'])
        with open(zip_path, 'wb') as f:
            f.write(zip_buffer.getvalue())
        
        # 统一返回JSON
            # 拼接所有模板的统计文本
            combined_stats = []
            for template_name, template_data in res.items():
                if 'total_txt' in template_data:
                    combined_stats.append(f"{template_name}\n{template_data['total_txt']}")
            
            return jsonify({
                'res': 'ok',
                'data': {
                    'total_orders': stats['total_orders'],
                    'success_orders': stats['success_orders'],
                    'failed_orders': stats['failed_orders'],
                    'products': [{'name':k, 'count':v} for k,v in stats['products'].items()],
                    'combined_stats_text': "\n\n".join(combined_stats),
                    'download_url': f'/download_orders?filename={stats["zip_filename"]}'
                }
            })


    def connect_mysql(self):
        with open(os.path.join(os.path.dirname(os.path.realpath((__file__))),'config','db.config'),'r',encoding='utf-8') as f:
            cfg=json.load(f)
        # 连接数据库
        conn = pymysql.connect(
            host=cfg['host'],       # 数据库主机地址
            user=cfg['user'],     # 数据库用户名
            password=cfg['password'], # 数据库密码
            database=cfg['database'],  # 要连接的数据库名称
            port=cfg['port']
        )

        return conn
    
   
    
    def zip_and_download_mix_ktt(self):        
        fn=request.args.get('fn').split('.')[0]
        date_input=fn.split('-')[0]
        batch_num=fn.split('.')[0].split('-')[3]
        path=os.path.join(self.ktt_config['ktt_temp_dir'],'to_sender_xlsx',f'{date_input[:4]}-{date_input[4:6]}-{date_input[6:]}')
        output_filename=f'{date_input[:4]}-{date_input[4:6]}-{date_input[6:]}-{batch_num}'

        files_in_path = [os.path.join(path, f) for f in os.listdir(path) if os.path.isfile(os.path.join(path, f))]
        # print('files in path :',files_in_path)
    
        # 如果只有一个文件且是xlsx类型，则直接返回该文件
        # if len(files_in_path) == 1 and files_in_path[0].lower().endswith('.xlsx'):
        #     return self.serve_single_file(files_in_path[0])

        memory_file = io.BytesIO()    
        try:
            with zipfile.ZipFile(memory_file, 'w', zipfile.ZIP_STORED) as zipf:
                for root, dirs, files in os.walk(path):
                    for file in files:
                        try:
                            if file.split('-')[1]==batch_num:
                                file_path = os.path.join(root, file)
                                arcname = os.path.relpath(file_path, path)
                                # print('zip in for:',file_path,arcname)
                                zipf.write(file_path, arcname)
                        except Exception as e:
                            print(f"Error writing file {file_path} to zip: {e}")
                            continue
        except Exception as e:
            print(f"Error creating zip file: {e}")
            return Response(f"Error creating zip file: {e}", status=500)

        memory_file.seek(0)

        return Response(memory_file.getvalue(),
                        mimetype='application/zip',
                        headers={'Content-Disposition': f'attachment;filename={output_filename}.zip'})

    

    def download_orders(self):
        filename = request.args.get('filename')
        filepath = os.path.join(self.ktt_config['export_root_dir'], 'temp', filename)
        if os.path.exists(filepath):
            return send_file(filepath, as_attachment=True)
        return jsonify({'res': 'failed', 'msg': '文件不存在'})

    def zip_and_download_ktt_exp_order(self):
        # prd_input=request.data.decode('utf-8')
        tmp_fn=os.path.join(self.ktt_config['save_dir'],'newfn.tmp')
        with open (tmp_fn, 'r') as f:
            newfn=f.read()
        return send_file(os.path.join(self.ktt_config['save_dir'],newfn),as_attachment=True)

    
class FERROR(Exception):
    pass


if __name__ == '__main__':
    app = EjService(__name__)
    # if len(sys.argv)>1:
    #     # print(f'服务器为：{sys.argv[1]}:5000')
    #     app.run(debug=True,host=sys.argv[1],port=5023)
    # else:
    #     app.run(debug=True)

    app.run(debug=True,host='127.0.0.1',port=5023)
    # app.run(debug=True,host='192.168.10.2',port=5000)
    # app.run(debug=True,host='192.168.1.41',port=5001)
    # app.run(debug=True,host='192.168.1.149',port=5000)
    # res=wecom_dir()
    # print(res)
